package com.lq.JDBC;

import com.lq.JDBC.Utils.DBUtils;
import com.lq.JDBC.entity.User;
import org.junit.Test;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

public class JDBCTest {
    @Test
    public void testConnection() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        //1.定义要素
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
        String driverName = "com.mysql.cj.jdbc.Driver";
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";

        //2.实例化Driver
        Class clazz = Class.forName(driverName);

//        Driver driver = (Driver) clazz.newInstance();
        //3.注册驱动，其实可以不写Class clazz = Class.forName(driverName);在创建的时候
        // 其静态代码块就已经注册了
//        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }

    @Test
    public void testInsert() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Properties properties = new Properties();
        //使用classloader来加载classpath下面的一个文件，加载的是当前工程下面的class,如果你把properties文件写在了某个包下面，则需要
        //补全包的名字，不然找不到，或者写在src下面
        properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));

        Class clazz = Class.forName(properties.getProperty("driverName"));

        Connection conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
        System.out.println(conn);

        Statement statement = conn.createStatement();
        String sql = "insert into user (id,username,password,birthday) values (2,'jerry2','1233','2000-09-09')";
        int affectedRows = statement.executeUpdate(sql);
        System.out.println(affectedRows);

    }

    /**
     * 将数据库中的每个数据有映射为java中的一个实例对象
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     */
    @Test
    public void testQuery() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Properties properties = new Properties();
        //使用classloader来加载classpath下面的一个文件，加载的是当前工程下面的class,如果你把properties文件写在了某个包下面，则需要
        //补全包的名字，不然找不到，或者写在src下面
        properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));

        Class clazz = Class.forName(properties.getProperty("driverName"));

        Connection conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
        System.out.println(conn);

        Statement statement = conn.createStatement();
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            int id = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String password = resultSet.getString(3);
            Date birthday = resultSet.getDate(4);
            user.setId(id);
            user.setUsername(username);
            user.setPassword(password);
            user.setBirthday(birthday);
            userList.add(user);
        }
        System.out.println(userList);
    }

    @Test
    public void testQuery2() {
        //抽离资源，方便合理关闭
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        //手动处理异常
        try {
            conn = DBUtils.getConnection();
            statement = conn.createStatement();
            String sql = "select * from user";
            resultSet = statement.executeQuery(sql);
            List<User> userList = new ArrayList<>();
            //使用遍历获取数据
            while (resultSet.next()){
                User user = new User();
                int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                Date birthday = resultSet.getDate(4);
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setBirthday(birthday);
                userList.add(user);
            }
            System.out.println(userList);

        } catch (Exception exception) {
            exception.printStackTrace();
        }finally {
            DBUtils.closeAll(conn, statement, resultSet);
        }
    }

    @Test
    public void testQuery3()  {

        // 1、定义资源
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        String sql = "select * from user where id = ?";

        try {
            // 获取连接
            connection = DBUtils.getConnection();
            // 获取使用预编译的statement
            statement = connection.prepareStatement(sql);
            statement.setInt(1,1);
            // 获取结果集
            resultSet = statement.executeQuery();
            // 封装结果
            List<User> users = new ArrayList<>();
            while (resultSet.next()){
                User user = new User();
                int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                Date date = resultSet.getDate(4);
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setBirthday(date);
                users.add(user);
            }
            System.out.println(users);
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            // 关闭资源
            DBUtils.closeAll(connection,statement,resultSet);
        }

    }


}
